<?php
class UserTable extends Doctrine_Table
{
    public function createSelect($required = true, $multiple = false)
    {
        $pairs = $this->fetchPairs('id', 'name');
        $select = new Zend_Form_Element_Select('user_id', array('multiple' => $multiple));
        $select
            ->setRequired($required)
            ->addMultioption('0', '---Select user---')
            ->addMultiOptions($pairs)
            ->setLabel('User');
        return $select;
    }

    public function fetchPairs($key = 'id', $name = 'name')
    {
        $query = $this->createQuery()
            ->select($key.', '.$name)
            ->orderBy($name)
            ->setHydrationMode(Doctrine::HYDRATE_ARRAY)
            ;

        $rows = $query->execute();

        $pairs = array();
        foreach ($rows as $row)
            $pairs[$row[$key]] = $row[$name];

        return $pairs;
    }

    public function fetchExtGridData($params)
    {
        $query = Doctrine_Query::create()
            ->setHydrationMode(Doctrine::HYDRATE_ARRAY)
            ->from('User u')
            ->leftJoin('u.UserGroup ug')
            ;

        //filter on group ID
        if($params['group_id'])
            $query->addWhere('ug.group_id = ?', $params['group_id'])
            ;

        $query_cnt = clone $query;

        $query_cnt
            ->select('COUNT(DISTINCT u.id) cnt')
            ;

        $query
            ->select('u.name, u.login, u.email, g.name')
            ->leftjoin('u.groups g')
            ->orderBy($params['sort'].' '.$params['dir'])
            ->limit($params['limit'])
            ->offset($params['offset'])
            ;

        $rawData = array();

        $users = $query->execute();
        $count = $query_cnt->fetchOne();

        $rawData['users'] = $users;
        $rawData['count'] = $count['cnt'];

        return $rawData;
    }
}
?>